********************************************************************************
** 	TITLE: b7_0_casenumbers_saginaw.do
**
**	PROJECT: IGNITE
** 
**  PURPOSE: Pulling unique case numbers from 
**	Saginaw_bookrecords_90_maininfo_casenrbond80.dta
** Note: Using the 99% sample gives the same results as in 90% sample.
********************************************************************************		
	
		set sortseed 13
		
	
/**********************************
	C. Bringing in Saginaw 
**********************************/	
	use "$output_data/saginaw_bookrecords_90_maininfo_casenrbond80.dta", clear 

	** removing space from case number strings
gen case_nr80 = subinstr(case_nr, " ", "", .)

duplicates drop case_nr80, force
drop if case_nr80 == "" //28,844 unique case numbers 


	** dropping the observations with incomplete case numbers
drop if strpos(case_nr80, "-") > 0 //drops 156 case numbers

//Defining criminal cases and post-Covid cases
*case codes Criminal and by year. 
*Circuit: AX; FC; FH; FJ; 
*District: EX, FY, OM, SM; 
local circcrim AX FC FH FJ
local distcrim EX FY OM SM
local crim `circcrim' `distcrim'

foreach x of local crim{
gen `x'_casecode = (strpos(case_nr80, "`x'") > 0)
} 

egen criminal = rowmax(`crim')

gen post_cov = (year>=2020)
codebook post_cov

tempfile original

sort year criminal

keep case_nr80 year post_cov criminal

summ post_cov criminal

summ year if post_cov == 1

summ year if post_cov == 0

codebook criminal if post_cov == 1

codebook criminal if post_cov == 0

saveold `original', replace

save "$output_data/saginaw_case_num_22dec2023.dta", replace


use `original', clear

keep if post_cov == 1 & criminal == 1

keep case_nr80
export excel case_nr80 using "$output_data/saginaw_case_postcrim_22dec2023.xls", sheetreplace

use `original', clear

keep if post_cov == 0 & criminal == 1

keep case_nr80
export excel case_nr80 using "$output_data/saginaw_case_precrim_22dec2023.xls", sheetreplace

use `original', clear

keep if criminal == 0

keep case_nr80
export excel case_nr80 using "$output_data/saginaw_case_noncrim_22dec2023.xls", sheetreplace